package com.software.sqlite.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.software.sqlite.entity.UserInfo;

import java.util.ArrayList;
import java.util.List;


/**
 *  应用程序启动时，会加载
 *  创建或打开数据库
 */

public class UserDBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "user.db";

    private static final int DB_VERSION = 1;

    private static final String TABLE_NAME = "user_info";
    //  类的静态属性，只会加载一次
    private static UserDBHelper userDBHelper;;
    private SQLiteDatabase read, write;

    private UserDBHelper (Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    //单例模式，懒汉模式 -> 饿汉模式
    //保证应用中有且只有一个实例，只有UserDBHelper，只有它才能操作user.db数据库
    public static UserDBHelper getInstance(Context context) {
        //如果是空的，则创建对象，否则直接返回
        if (userDBHelper == null) {
            userDBHelper = new UserDBHelper(context);
        }
        return userDBHelper;
    }
    // 提供两个连接方式，读和写
    public SQLiteDatabase openReadLink() {
        if (read == null || !read.isOpen()) {
            read = userDBHelper.getReadableDatabase();
        }

        return read;
    }

    public SQLiteDatabase openWriteLink() {
        if (write == null || !write.isOpen()) {
            write = userDBHelper.getWritableDatabase();
        }
        return write;
    }

    // 关闭连接
    public void closeLink() {
        if (read != null || read.isOpen()) {
            read.close();
            read = null;
        }
        if (write != null || write.isOpen()) {
            write.close();
            write = null;
        }
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( " +
                " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                " name VARCHAR NOT NULL, " +
                " age INT NOT NULL, " +
                " height INT NOT NULL, " +
                " weight FLOAT NOT NULL );";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 数据库版本发生变化的时候，应用程序安装启动会执行此方法
        // 更新数据库表结构
        String sql = "ALTER TABLE "+ TABLE_NAME +" ADD COLUMN phone VARCHAR;";
        db.execSQL(sql);
    }

    //新增数据
    public long insert(UserInfo userInfo) {
        // 如果插入的值为空
        // insert into user_info() values()
        // nullColumnHack必须有值
        // write.insert(TABLE_NAME, "age", null);
        // insert into user_info(age) values(null)

        ContentValues values = new ContentValues();
        values.put("name", userInfo.getName());
        values.put("age", userInfo.getAge());
        values.put("height", userInfo.getHeight());
        values.put("weight", userInfo.getWeight());
        return write.insert(TABLE_NAME, null, values);
    }

    public UserInfo queryById(String id) {
        Cursor cursor = read.query(TABLE_NAME, null, "id = ?", new String[]{id}, null, null, null);
        // 相当于ResultSet
        // 如果游标可以移动到第一行，说明有数据
        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndex("id");
            int dbId = cursor.getInt(idIndex);

            int nameIndex = cursor.getColumnIndex("name");
            String name = cursor.getString(nameIndex);

            int ageIndex = cursor.getColumnIndex("age");
            int age = cursor.getInt(ageIndex);

            int heightIndex = cursor.getColumnIndex("height");
            int height = cursor.getInt(heightIndex);

            int weightIndex = cursor.getColumnIndex("weight");
            float weight = cursor.getFloat(weightIndex);

            UserInfo userInfo = new UserInfo();
            userInfo.setId(dbId);
            userInfo.setName(name);
            userInfo.setAge(age);
            userInfo.setHeight(height);
            userInfo.setWeight(weight);
            return userInfo;
        }
        return null;

    }

    public List<UserInfo> queryAll() {
        Cursor cursor = read.query(TABLE_NAME, null, null, null, null, null, null);
        List<UserInfo> userInfoList = new ArrayList<>();
        if (cursor.moveToFirst()) {
            do {
                //取数据
                int id = cursor.getInt(0);
                String name = cursor.getString(1);
                int age = cursor.getInt(2);
                int height = cursor.getInt(3);
                float weight = cursor.getFloat(4);
                UserInfo userInfo = new UserInfo();
                userInfo.setId(id);
                userInfo.setName(name);
                userInfo.setAge(age);
                userInfo.setHeight(height);
                userInfo.setWeight(weight);
                userInfoList.add(userInfo);
            } while (cursor.moveToNext());
        }
        return null;
    }

    public long delete(String id) {
        return write.delete(TABLE_NAME, "id = ?", new String[]{id});
    }

    public long update(UserInfo userInfo) {
        ContentValues values = new ContentValues();
        values.put("name", userInfo.getName());
        values.put("age", userInfo.getAge());
        values.put("height", userInfo.getHeight());
        values.put("weight", userInfo.getWeight());
        return write.update(TABLE_NAME, values, "id = ?", new String[]{String.valueOf(userInfo.getId())});
    }
}
